#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <time.h>
#include <ctype.h>

// 数据库连接全局变量
sqlite3 *db;

// 密码加密
void encrypt_password(const char *password, char *encrypted) {
    int i = 0;
    while (password[i]) {
        encrypted[i] = password[i] + 10; // 简单加密
        i++;
    }
    encrypted[i] = '\0';
}

// 初始化数据库
int init_database() {
    int rc = sqlite3_open("student.db", &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db));
        return 0;
    }
    
    // 创建表结构
    const char *sql_create_users = 
        "CREATE TABLE IF NOT EXISTS Users ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "username TEXT UNIQUE NOT NULL, "
        "password TEXT NOT NULL, "
        "is_admin INTEGER DEFAULT 0)";
    
    const char *sql_create_students = 
        "CREATE TABLE IF NOT EXISTS Students ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "sno TEXT UNIQUE NOT NULL, "
        "name TEXT NOT NULL, "
        "class_name TEXT)";
    
    const char *sql_create_scores = 
        "CREATE TABLE IF NOT EXISTS Scores ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "sno TEXT NOT NULL, "
        "subject TEXT NOT NULL, "
        "score REAL, "
        "FOREIGN KEY(sno) REFERENCES Students(sno))";
    
    char *err_msg = 0;
    rc = sqlite3_exec(db, sql_create_users, NULL, 0, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建用户表失败: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 0;
    }
    
    rc = sqlite3_exec(db, sql_create_students, NULL, 0, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建学生表失败: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 0;
    }
    
    rc = sqlite3_exec(db, sql_create_scores, NULL, 0, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "创建成绩表失败: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 0;
    }
    
    // 初始化管理员账户
    sqlite3_stmt *stmt;
    rc = sqlite3_prepare_v2(db, "SELECT COUNT(*) FROM Users WHERE is_admin=1", -1, &stmt, 0);
    
    if (rc == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            int count = sqlite3_column_int(stmt, 0);
            
            if (count == 0) {
                char encrypted[100];
                encrypt_password("admin123", encrypted);
                char sql[200];
                snprintf(sql, sizeof(sql),
                    "INSERT INTO Users(username, password, is_admin) "
                    "VALUES('admin', '%s', 1)", encrypted);
                sqlite3_exec(db, sql, NULL, 0, &err_msg);
                printf("初始化管理员账户: 用户名=admin, 密码=admin123\n");
            }
        }
        sqlite3_finalize(stmt);
    }
    
    return 1;
}

// 用户登录验证
int authenticate_user(const char *username, const char *password) {
    char encrypted[100];
    encrypt_password(password, encrypted);
    
    sqlite3_stmt *stmt;
    const char *sql = "SELECT id FROM Users WHERE username = ? AND password = ?";
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    
    if (rc != SQLITE_OK) {
        fprintf(stderr, "准备SQL语句失败: %s\n", sqlite3_errmsg(db));
        return 0;
    }
    
    sqlite3_bind_text(stmt, 1, username, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, encrypted, -1, SQLITE_STATIC);
    
    rc = sqlite3_step(stmt);
    int result = (rc == SQLITE_ROW);
    
    sqlite3_finalize(stmt);
    return result;
}

// 帮助菜单
void Help() {
    printf("****************************************************\n");
    printf("  *             学生管理系统——帮助菜单             *   \n");
    printf("  *              H=显示帮助菜单                   *   \n");
    printf("  *              T=成绩录入                      *   \n");
    printf("  *              A=计算学生平均分                 *   \n");
    printf("  *              L=列出成绩表                    *   \n");
    printf("  *              P=按平均成绩由高到低排序          *   \n");
    printf("  *              S=按学号查询学生成绩             *   \n");
    printf("  *              C=清屏                         *   \n");
    printf("  *              Q=退出系统                     *    \n");
    printf("****************************************************\n");
}

// 成绩录入
int Type() {
    int n;
    printf("请输入学生人数:");
    scanf("%d", &n);
    if (n <= 0) {
        printf("无效的学生人数\n");
        return 0;
    }
    
    char *err_msg = 0;
    sqlite3_exec(db, "BEGIN TRANSACTION", NULL, 0, &err_msg);
    
    for (int i = 0; i < n; i++) {
        char sno[20], name[50], class_name[50];
        float chinese, math, english;
        
        printf("请输入第 %d 个学生信息 (学号 姓名 班级 语文 数学 外语):\n", i+1);
        scanf("%s %s %s %f %f %f", sno, name, class_name, &chinese, &math, &english);
        
        // 插入学生信息
        char sql[500];
        snprintf(sql, sizeof(sql), 
            "INSERT INTO Students(sno, name, class_name) VALUES('%s', '%s', '%s')",
            sno, name, class_name);
        sqlite3_exec(db, sql, NULL, 0, &err_msg);
        
        // 插入成绩信息
        snprintf(sql, sizeof(sql), 
            "INSERT INTO Scores(sno, subject, score) VALUES('%s', '语文', %f)",
            sno, chinese);
        sqlite3_exec(db, sql, NULL, 0, &err_msg);
        
        snprintf(sql, sizeof(sql), 
            "INSERT INTO Scores(sno, subject, score) VALUES('%s', '数学', %f)",
            sno, math);
        sqlite3_exec(db, sql, NULL, 0, &err_msg);
        
        snprintf(sql, sizeof(sql), 
            "INSERT INTO Scores(sno, subject, score) VALUES('%s', '外语', %f)",
            sno, english);
        sqlite3_exec(db, sql, NULL, 0, &err_msg);
    }
    
    sqlite3_exec(db, "COMMIT", NULL, 0, &err_msg);
    printf("成绩录入完成\n");
    return n;
}

// 计算平均分回调函数
static int avg_callback(void *NotUsed, int argc, char **argv, char **azColName) {
    printf("学号: %s, 平均分: %.1f\n", argv[0], atof(argv[1]));
    return 0;
}

// 计算平均分
void Average() {
    const char *sql = 
        "SELECT s.sno, AVG(sc.score) AS average "
        "FROM Students s "
        "JOIN Scores sc ON s.sno = sc.sno "
        "GROUP BY s.sno";
    
    char *err_msg = 0;
    int rc = sqlite3_exec(db, sql, avg_callback, 0, &err_msg);
    
    if (rc != SQLITE_OK) {
        fprintf(stderr, "计算平均分失败: %s\n", err_msg);
        sqlite3_free(err_msg);
    }
}

// 列出成绩表回调函数
static int list_callback(void *NotUsed, int argc, char **argv, char **azColName) {
    printf("%s    %s    %s    %s    %.1f\n", 
           argv[0], argv[1], argv[2], argv[3], atof(argv[4]));
    return 0;
}

// 列出成绩表
void List() {
    const char *sql = 
        "SELECT s.sno, "
        "MAX(CASE WHEN sc.subject = '语文' THEN sc.score ELSE 0 END) AS chinese, "
        "MAX(CASE WHEN sc.subject = '数学' THEN sc.score ELSE 0 END) AS math, "
        "MAX(CASE WHEN sc.subject = '外语' THEN sc.score ELSE 0 END) AS english, "
        "AVG(sc.score) AS average "
        "FROM Students s "
        "JOIN Scores sc ON s.sno = sc.sno "
        "GROUP BY s.sno";
    
    printf("学生成绩如下：\n学号  语文  数学  外语  平均分\n");
    
    char *err_msg = 0;
    int rc = sqlite3_exec(db, sql, list_callback, 0, &err_msg);
    
    if (rc != SQLITE_OK) {
        fprintf(stderr, "列出成绩表失败: %s\n", err_msg);
        sqlite3_free(err_msg);
    }
}

// 按平均分排序
void Sort() {
    const char *sql = 
        "SELECT s.sno, "
        "MAX(CASE WHEN sc.subject = '语文' THEN sc.score ELSE 0 END) AS chinese, "
        "MAX(CASE WHEN sc.subject = '数学' THEN sc.score ELSE 0 END) AS math, "
        "MAX(CASE WHEN sc.subject = '外语' THEN sc.score ELSE 0 END) AS english, "
        "AVG(sc.score) AS average "
        "FROM Students s "
        "JOIN Scores sc ON s.sno = sc.sno "
        "GROUP BY s.sno "
        "ORDER BY average DESC";
    
    printf("学生成绩排序如下：\n学号  语文  数学  外语  平均分\n");
    
    char *err_msg = 0;
    int rc = sqlite3_exec(db, sql, list_callback, 0, &err_msg);
    
    if (rc != SQLITE_OK) {
        fprintf(stderr, "排序失败: %s\n", err_msg);
        sqlite3_free(err_msg);
    }
}

// 按学号查询学生成绩
void Search() {
    char sno[20];
    printf("请输入要查询的学生学号：");
    scanf("%s", sno);
    
    const char *sql = 
        "SELECT s.sno, "
        "MAX(CASE WHEN sc.subject = '语文' THEN sc.score ELSE 0 END) AS chinese, "
        "MAX(CASE WHEN sc.subject = '数学' THEN sc.score ELSE 0 END) AS math, "
        "MAX(CASE WHEN sc.subject = '外语' THEN sc.score ELSE 0 END) AS english, "
        "AVG(sc.score) AS average "
        "FROM Students s "
        "JOIN Scores sc ON s.sno = sc.sno "
        "WHERE s.sno = ? "
        "GROUP BY s.sno";
    
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    
    if (rc != SQLITE_OK) {
        fprintf(stderr, "准备查询语句失败: %s\n", sqlite3_errmsg(db));
        return;
    }
    
    sqlite3_bind_text(stmt, 1, sno, -1, SQLITE_STATIC);
    
    printf("学号  语文  数学  外语  平均分\n");
    
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        printf("%s    %.1f    %.1f    %.1f    %.1f\n",
               sqlite3_column_text(stmt, 0),
               sqlite3_column_double(stmt, 1),
               sqlite3_column_double(stmt, 2),
               sqlite3_column_double(stmt, 3),
               sqlite3_column_double(stmt, 4));
    }
    
    if (rc != SQLITE_DONE) {
        fprintf(stderr, "执行查询失败: %s\n", sqlite3_errmsg(db));
    }
    
    sqlite3_finalize(stmt);
}

int main() {
    if (!init_database()) {
        return 1;
    }
    
    // 用户登录
    char username[50], password[50];
    int logged_in = 0;
    
    while (!logged_in) {
        printf("请输入用户名: ");
        scanf("%s", username);
        printf("请输入密码: ");
        scanf("%s", password);
        
        if (authenticate_user(username, password)) {
            printf("登录成功！欢迎 %s\n", username);
            logged_in = 1;
        } else {
            printf("用户名或密码错误，请重试\n");
        }
    }
    
    Help();
    
    char command;
    while (1) {
        printf("请输入命令=");
        while (getchar() != '\n');
        scanf("%c", &command);
        
        switch (command) {
            case 'C':
                system("clear");
                break;
            case 'H':
                Help();
                break;
            case 'Q':
                sqlite3_close(db);
                return 0;
            case 'T':
                Type();
                break;
            case 'L':
                List();
                break;
            case 'A':
                Average();
                break;
            case 'P':
                Sort();
                break;
            case 'S':
                Search();
                break;
            default:
                printf("未知命令，请重新输入\n");
        }
    }
    
    sqlite3_close(db);
    return 0;
}